// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');


// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'bookstore1',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');
const { query } = require('express');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080','http://localhost:8081', 'http://127.0.0.1:8080','http://localhost:8082','http://localhost:8083','http://localhost:8084','http://localhost:8085']
}));

//获取所有书籍
server.get('/AllBooks', (req, res) => {
  //获取用户名和密码信息
  // SQL语句
  let sql = `SELECT BookName FROM books`;
  pool.query(sql, (error, datas) => {
    if (error) throw error;
    res.send({message:'成功',code:200,results:{datas}});
  });
});
//获取所有用户
server.get('/AllUser', (req, res) => {
  //获取用户名和密码信息
  // SQL语句
  let sql = `SELECT uname FROM users`;
  pool.query(sql, (error, datas) => {
    if (error) throw error;
    res.send({message:'成功',code:200,results:{datas}});
  });
});
//用户获取图书信息接口
server.get('/books', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM books where State=0`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM books where State=0 limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });
//管理员获取图书信息接口
server.get('/BookSet', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM books`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM books  limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

//添加图书接口
server.get('/AddBooks', (req, res) => {
  let BookName = req.query.BookName;
  let BookAuthor = req.query.BookAuthor;
  let BookLocation = req.query.BookLocation;
  let BookSum = req.query.BookSum;
  // SQL语句
  let sql = 'SELECT * from books WHERE BookName=?';
  pool.query(sql, [BookName], (error, results) => {
    if (error) throw error;
    let count = results.length;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
  sql = `insert into books values(null,'《${BookName}》','${BookAuthor}','${BookLocation}','${BookSum}',0)`;
  // console.log(sql)
      pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: '添加书籍成功', code: 200 });
      })
    } else {
      res.send({ message: '该书籍已经添加过了', code: 201 });
    }
  });
  });

  //删除图书接口
  server.get('/DeleteBooks', (req, res) => {
    let Bid = req.query.Bid;
    // SQL语句
    let sql = `delete from books WHERE Bid=${Bid}`;
    pool.query(sql, (error, results) => {
      if (error) throw error;
      res.send({ message: '删除成功', code: 200 });
    });
  });
  //上架/下架图书接口
  server.get('/SetState', (req, res) => {
    let Bid = req.query.Bid;
    let State = (req.query.State==0?1:0)*1;
    // console.log("上架还是下架",State)
    // SQL语句
    let sql = `update books set State=${State} WHERE Bid=${Bid}`;
    pool.query(sql, (error, results) => {
      if (error) throw error;
      if(State==0){
      res.send({ message: '图书上架成功', code: 200 });
      }else{
        res.send({ message: '图书下架成功', code: 200 });
      }
    });

  });

//用户注册接口
server.post('/reginster', (req, res) => {
  // 获取用户名和密码信息
  // console.log(req.query)
  let uname = req.query.uname;
  let pwd = req.query.upwd;
  let phone=req.query.phone
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT * from users WHERE uname=?';
  pool.query(sql, [uname], (error, results) => {
    if (error) throw error;
    let count = results.length;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = `INSERT INTO users VALUES(null,'${uname}','${pwd}',${phone})`;
      pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: '注册成功', code: 200 });
      })
    } else {
      res.send({ message: '用户名重复', code: 201 });
    }
  });
});

// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let uname = req.query.uname;
  let upwd = req.query.upwd;
  // SQL语句
  let sql = `SELECT * from users WHERE uname='${uname}' AND upwd='${upwd}'`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    if(results.length == 0){ //登录失败
      res.send({message:'登录失败',code:201});
    } else {                 //登录成功
      res.send({message:'登录成功',code:200,result:results[0]});  
    }
  });

});

// 管理员登录接口
server.post('/Adminlogin', (req, res) => {
  //获取用户名和密码信息
  let uname = req.query.uname;
  let upwd = req.query.upwd;
  // SQL语句
  let sql = `SELECT * from admin WHERE AdminUser='${uname}' AND AdminPwd='${upwd}'`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    if(results.length == 0){ //登录失败
      res.send({message:'登录失败',code:201});
    } else {                 //登录成功
      res.send({message:'登录成功',code:200,result:results[0]});
    }
  });

});

//管理员添加接口
server.post('/AdminAdd', (req, res) => {
  // 获取用户名和密码信息
  // console.log(req.query)
  let AdminUser = req.query.AdminUser;
  let AdminPwd= req.query.AdminPwd;
  let phone=req.query.phone;
  let RealName=req.query.RealName;
  let AdminArea=req.query.AdminArea;
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT * from admin WHERE AdminUser=?';
  pool.query(sql, [AdminUser], (error, results) => {
    if (error) throw error;
    let count = results.length;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = `INSERT INTO admin VALUES(null,'${AdminUser}','${AdminPwd}','${RealName}','${phone}','${AdminArea}')`;
      pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: '注册成功', code: 200 });
      })
    } else {
      res.send({ message: '用户名重复', code: 201 });
    }
  });
});

//注销管理员账号接口
server.get('/AdminDelete', (req, res) => {
  let Aid = req.query.Aid;
  // console.log(req.query)
  // SQL语句
  let sql = `delete from admin where Aid=${Aid}`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({message:'删除成功',code:200});
  });
});


//获取管理员信息
server.get('/GetAdmin', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM admin`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM admin limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

  //用户查询预约记录
server.get('/ApplyRecord', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
  var ApplyName=req.query.name
  // console.log(ApplyName)
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM applybook where ApplyName='${ApplyName}'`;
      pool.query(sql,(error, result) => {
        // console.log(result)
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM applybook where ApplyName='${ApplyName}' limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

//获取预约申请信息
server.get('/GetApplyBook', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM applybook`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM applybook limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

//同意用户申请
server.get('/Agree', (req, res) => {
  //获取用户名和密码信息
  let ABid = req.query.ABid;
  // SQL语句
  let sql = `update applybook set State=0 where ABid=${ABid}`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({message:'成功',code:200});
  });
});

//拒绝用户申请
server.get('/Refuse', (req, res) => {
  //获取用户名和密码信息
  let ABid = req.query.ABid;
  // SQL语句
  let sql = `update applybook set State=1 where ABid=${ABid}`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({message:'成功',code:200});
  });
});

  //用户预约书籍
  server.get('/AddApply', (req, res) => {
    let BookName = req.query.BookName;
    let ApplyName = req.query.ApplyName;
    let UserAdress = req.query.UserAddress;
    let ApplyTime = req.query.ApplyTime;
    // console.log(res)
    // console.log(BookName,ApplyName,UserAdress,ApplyTime)
    // SQL语句
    let sql = `insert into applybook values(null,'${BookName}','${ApplyName}','${UserAdress}','${ApplyTime}',2)`;
    pool.query(sql, (error, results) => {
      if (error) throw error;
      res.send({ message: '预约成功', code: 200 });
    });

  });

  //管理员添加用户借用书籍
  server.get('/UserAddBorrow', (req, res) => {
    let BorrowBook = req.query.BorrowBook;
    let BorrowUser = req.query.BorrowUser;
    let ApplyStartTime = req.query.ApplyStartTime;
    let ApplyOverTime = req.query.ApplyOverTime;
    // console.log(res)
    var sql = `SELECT phone FROM  users where uname='${BorrowUser}'`;
    pool.query(sql,(error, result) => {
      if (error) throw error;
      // console.log(result)
      let phone=result[0].phone
      let sql = `insert into borrowbooks values(null,'${BorrowBook}','${BorrowUser}','${phone}','${ApplyStartTime}','${ApplyOverTime}',0)`;
      pool.query(sql,(error, datas) => {
        if (error) throw error;
        // 让书籍的库存-1
        var sql = `update books set BooksSum=BooksSum-1 where BookName='${BorrowBook}';`;
        pool.query(sql,(error,result)=>{
        if (error) throw error;
          // console.log(result)
          res.send({ message: '借用成功', code: 200});
        });
      });
      // SQL语句
    });
    })

  //管理员添加非用户借用书籍
  server.get('/NoUserAddBorrow', (req, res) => {
    let BorrowBook = req.query.BorrowBook;
    let BorrowUser = req.query.BorrowUser;
    let ApplyStartTime = req.query.ApplyStartTime;
    let ApplyOverTime = req.query.ApplyOverTime;
    let phone=req.query.Phone
    // console.log(BorrowBook)
    // SQL语句
    let sql = `insert into borrowbooks values(null,'${BorrowBook}','${BorrowUser}','${phone}','${ApplyStartTime}','${ApplyOverTime}',0)`;
    pool.query(sql, (error, results) => {
      if (error) throw error;
      // 让书籍的库存-1
      var sql = `update books set BooksSum=BooksSum-1 where BookName='${BorrowBook}';`;
      pool.query(sql,(error,result)=>{
      if (error) throw error;
      res.send({ message: '借用成功', code: 200 });
    });
  });
});

  //获取借用历史状态信息
server.get('/GetBorrowBooks', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM borrowbooks`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM borrowbooks limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

  //获取用户借用历史状态信息
server.get('/GetUserBorrowBooks', (req, res) => {
  var page=(req.query.page>1?req.query.page:1)*1
  var mepage=page-1
  var shuju=8
  var BorrowUser=req.query.name
    // if (error) throw error;
      // 向上取整 只要有余数 就+1
      var sql = `SELECT * FROM borrowbooks where BorrowUser="${BorrowUser}"`;
      pool.query(sql,(error, result) => {
  let pagecount=Math.ceil(result.length/8) 
  var sql = `SELECT * FROM borrowbooks where BorrowUser="${BorrowUser}" limit ${mepage*shuju},${shuju}`;
  
  pool.query(sql,(error, datas) => {
    if (error) throw error;
    // console.log("总页数",pagecount)
    // console.log("当前页",page)
    res.send({ message: 'ok', code: 200, results:{datas,page,pagecount} });
  });
});
  });

  //归还借用书籍
server.get('/ReturnBook', (req, res) => {
  //获取用户名和密码信息
  let Bid = req.query.Bid;
  let BookName = req.query.BookName;
  // console.log(BookName)
  // SQL语句
  let sql = `update borrowbooks set State=1 where Bid=${Bid}`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    var sql = `update books set BooksSum=BooksSum+1 where BookName='${BookName}';`;
    pool.query(sql,(error,result)=>{
    if (error) throw error;
    res.send({message:'成功',code:200});
    
  });
});
});


// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('服务器跑起来了');
});